--
-- ER/Studio 8.0 SQL Code Generation
-- Company :      Microsoft
-- Project :      部委选学报名系统设计.DM1
-- Author :       AutoBVT
--
-- Date Created : Monday, January 04, 2016 18:00:42
-- Target DBMS : Oracle 11g
--

-- 
-- TABLE: BASE_ATTACHMENT 
--

CREATE TABLE BASE_ATTACHMENT(
    ATTACHMENT_ID          VARCHAR2(50)     NOT NULL,
    FILE_NAME              VARCHAR2(255),
    PRIMEVAL_FILE_NAME     VARCHAR2(255),
    GROUP_CODE             VARCHAR2(200),
    FILE_SIZE              NUMBER(38, 0),
    FILE_TYPE              VARCHAR2(50),
    MIME_TYPE              VARCHAR2(255),
    UPLOAD_DATE            TIMESTAMP(6),
    MODIFIABILITY_SCOPE    NUMBER(38, 0),
    DOWNLOAD_NUMBER        NUMBER(38, 0),
    UPLOAD_USER            VARCHAR2(50),
    CONTENT                BLOB,
    CONSTRAINT PK_BASE_ATTACHMENT PRIMARY KEY (ATTACHMENT_ID)
)
;

-- 添加指定报名单位关联表
create table CLASS_SCOPE_DEPARTMENT
(
  scope_dept_id VARCHAR2(50) not null,
  class_id      VARCHAR2(50),
  dept_id       VARCHAR2(50)
);
alter table CLASS_SCOPE_DEPARTMENT
  add constraint SCOPE_PK_ID primary key (SCOPE_DEPT_ID);
  alter table CLASS_SCOPE_DEPARTMENT
  add constraint CLASS_FK_ID foreign key (CLASS_ID)
  references TRAINING_CLASS (CLASS_ID);
alter table CLASS_SCOPE_DEPARTMENT
  add constraint DEPT_FK_ID foreign key (DEPT_ID)
  references BASE_DEPARTMENT (DEPID);

  --添加承办部委关联表
create table CLASS_SIGN_UP_DEPARTMENT
(
  SIGN_UP_dept_id VARCHAR2(50) not null,
  class_id      VARCHAR2(50),
  dept_id       VARCHAR2(50)
);
alter table CLASS_SIGN_UP_DEPARTMENT
  add constraint SCOPE_PK_ID2 primary key (SIGN_UP_dept_id);
  alter table CLASS_SIGN_UP_DEPARTMENT
  add constraint CLASS_FK_ID2 foreign key (CLASS_ID)
  references TRAINING_CLASS (CLASS_ID);
alter table CLASS_SIGN_UP_DEPARTMENT
  add constraint DEPT_FK_ID2 foreign key (DEPT_ID)
  references BASE_DEPARTMENT (DEPID);

-- 
-- TABLE: BASE_AUDIT_CONDITION 
--

CREATE TABLE BASE_AUDIT_CONDITION(
    CONDITION_ID         VARCHAR2(50)     NOT NULL,
    MODULE_NAME          VARCHAR2(255),
    MODULE_CODE          VARCHAR2(255),
    MODULE_CLASS_NAME    VARCHAR2(255),
    ACTION_TYPE          NUMBER(38, 0),
    CONSTRAINT PK_BASE_AUDIT_CONDITION PRIMARY KEY (CONDITION_ID)
)
;



-- 
-- TABLE: BASE_AUDIT_LOG 
--

CREATE TABLE BASE_AUDIT_LOG(
    AUDITLOG_ID       VARCHAR2(50)      NOT NULL,
    MODULE_NAME       VARCHAR2(100),
    ACTION_NAME       VARCHAR2(100),
    ACTION_TYPE       NUMBER(38, 0),
    EXECUTE_DATE      TIMESTAMP(6),
    USER_ID           VARCHAR2(50),
    USER_NAME         VARCHAR2(100),
    USER_IP           VARCHAR2(50),
    CHANGE_CONTEXT    VARCHAR2(4000),
    ACTION_URL        VARCHAR2(255),
    CONSTRAINT PK_BASE_AUDIT_LOG PRIMARY KEY (AUDITLOG_ID)
)
;



-- 
-- TABLE: BASE_BASEDATA 
--

CREATE TABLE BASE_BASEDATA(
    DATA_ID         VARCHAR2(255)    NOT NULL,
    DATA_CODE       VARCHAR2(255),
    DATA_NAME       VARCHAR2(100),
    TYPE_ID         VARCHAR2(255),
    ACTIVE_STATE    NUMBER(38, 0),
    ORDER_NUM       NUMBER(38, 0),
    CREATE_TIME     DATE,
    CREATOR         NUMBER(38, 0),
    CONSTRAINT PK_BASE_BASEDATA PRIMARY KEY (DATA_ID)
)
;



-- 
-- TABLE: BASE_BASEDATA_TYPE 
--

CREATE TABLE BASE_BASEDATA_TYPE(
    TYPE_ID         VARCHAR2(255)    NOT NULL,
    TYPE_CODE       VARCHAR2(255),
    PARENT_ID       VARCHAR2(255),
    TREEPATH        VARCHAR2(100),
    TYPE_NAME       VARCHAR2(255),
    ACTIVE_STATE    NUMBER(38, 0),
    ORDER_NUM       VARCHAR2(10),
    CONSTRAINT PK_BASE_BASEDATA_TYPE PRIMARY KEY (TYPE_ID)
)
;



-- 
-- TABLE: BASE_BLOB 
--

CREATE TABLE BASE_BLOB(
    BLOB_ID     VARCHAR2(255)    NOT NULL,
    ATT_NAME    VARCHAR2(500),
    ATT_EXT     VARCHAR2(30),
    ATT_BLOB    BLOB,
    CONSTRAINT PK_BASE_BLOB PRIMARY KEY (BLOB_ID)
)
;



-- 
-- TABLE: BASE_CLOB 
--

CREATE TABLE BASE_CLOB(
    CLOB_ID         VARCHAR2(255)    NOT NULL,
    CLOB_CONTENT    CLOB,
    CONSTRAINT PK_BASE_CLOB PRIMARY KEY (CLOB_ID)
)
;



-- 
-- TABLE: BASE_CONFIG_CATEGORY 
--

CREATE TABLE BASE_CONFIG_CATEGORY(
    CONFIG_CATEGORY_ID      VARCHAR2(50)     NOT NULL,
    CONFIG_CATEGORY_NAME    VARCHAR2(255),
    CONFIG_CATEGORY_CODE    VARCHAR2(255),
    CONSTRAINT PK_BASE_CONFIG_CATEGORY PRIMARY KEY (CONFIG_CATEGORY_ID)
)
;



-- 
-- TABLE: BASE_CONFIG_ITEM 
--

CREATE TABLE BASE_CONFIG_ITEM(
    CONFIG_ITEM_ID        VARCHAR2(50)     NOT NULL,
    CONFIG_ITEM_NAME      VARCHAR2(255),
    CONFIG_ITEM_CODE      VARCHAR2(255),
    CONFIG_ITEM_TYPE      VARCHAR2(255),
    CONFIG_ITEM_VALUE     VARCHAR2(255),
    ITEM_CAN_DELETE       NUMBER(38, 0),
    ITEM_CAN_EDIT         NUMBER(38, 0),
    CUSTOMIZE             NUMBER(38, 0),
    ITEM_NEED_RESTART     NUMBER(38, 0),
    CONFIG_CATEGORY_ID    VARCHAR2(50)     NOT NULL,
    CONSTRAINT PK_BASE_CONFIG_ITEM PRIMARY KEY (CONFIG_ITEM_ID)
)
;



-- 
-- TABLE: BASE_DATA_TRANSFER_LOG 
--

CREATE TABLE BASE_DATA_TRANSFER_LOG(
    LOGID             VARCHAR2(255)     NOT NULL,
    STORAGE_NAME      VARCHAR2(255),
    REMARK_CONTENT    VARCHAR2(1000),
    TRANSACT_DATE     DATE,
    RESULT            NUMBER(38, 0),
    CONSTRAINT PK_BASE_DATA_TRANSFER_LOG PRIMARY KEY (LOGID)
)
;



-- 
-- TABLE: BASE_DEPARTMENT 
--

CREATE TABLE BASE_DEPARTMENT(
    DEPID               VARCHAR2(255)     NOT NULL,
    DEPNAME             VARCHAR2(255)     NOT NULL,
    COMPANY_LEVEL       NUMBER(38, 0),
    DEPT_CODE           VARCHAR2(255),
    DEPT_ADDRESS        VARCHAR2(255),
    DEPT_POST_NUM       VARCHAR2(50),
    DEPT_PHONE          VARCHAR2(50),
    LINK_MAN            VARCHAR2(255),
    ORDER_NUM           NUMBER(38, 0),
    DEPKIND_CODE        VARCHAR2(25),
    TREEPATH            VARCHAR2(2000),
    PARENT_ID           VARCHAR2(255),
    REPORT_DEPID        NUMBER(38, 0),
    ACTIVE_STATE        NUMBER(38, 0)     DEFAULT 0,
    REMARK              VARCHAR2(300),
    BELONG_TO_REGION    VARCHAR2(255),
    OPERATE_TIME        TIMESTAMP(6),
    COUNTIESCODING      VARCHAR2(255),
    COUNTIESNAME        VARCHAR2(255),
    ISINDIVIDUATION     NUMBER(38, 0),
    CONSTRAINT PK_BASE_DEPARTMENT PRIMARY KEY (DEPID)
)
;



-- 
-- TABLE: BASE_DEPARTMENT_USER 
--

CREATE TABLE BASE_DEPARTMENT_USER(
    DEPUSER_ID      VARCHAR2(255)    NOT NULL,
    DEPID           VARCHAR2(255),
    OLD_DEPTID      VARCHAR2(255),
    ACTIVE_STATE    NUMBER(38, 0)    DEFAULT 0 NOT NULL,
    ORDER_NUM       NUMBER(38, 0),
    USERID          VARCHAR2(255),
    OPERATE_TIME    TIMESTAMP(6),
    CONSTRAINT PK_BASE_DEPARTMENT_USER PRIMARY KEY (DEPUSER_ID)
)
;



-- 
-- TABLE: BASE_LOCAL_CONFIG_ITEM 
--

CREATE TABLE BASE_LOCAL_CONFIG_ITEM(
    LOCAL_CONFIG_ITEM_ID       VARCHAR2(50)     NOT NULL,
    CONFIG_ITEM_ID             VARCHAR2(50)     NOT NULL,
    USERID                     VARCHAR2(255),
    LOCAL_CONFIG_ITEM_VALUE    VARCHAR2(255),
    CONSTRAINT PK_BASE_LOCAL_CONFIG_ITEM PRIMARY KEY (LOCAL_CONFIG_ITEM_ID)
)
;



-- 
-- TABLE: BASE_MAIL_SEND_LOG 
--

CREATE TABLE BASE_MAIL_SEND_LOG(
    LOG_ID             VARCHAR2(255)     NOT NULL,
    SEND_ADDRESS       VARCHAR2(255),
    RECEIVE_ADDRESS    VARCHAR2(2000),
    SEND_DATE          TIMESTAMP(6),
    MAIL_SUBJECT       VARCHAR2(255),
    MAIL_CONTENT       CLOB,
    SEND_STATE         NUMBER(38, 0),
    CONSTRAINT PK_BASE_MAIL_SEND_LOG PRIMARY KEY (LOG_ID)
)
;



-- 
-- TABLE: BASE_MAIL_SERVER_CONFIG 
--

CREATE TABLE BASE_MAIL_SERVER_CONFIG(
    CONFIG_ID       VARCHAR2(255)    NOT NULL,
    CONFIG_NAME     VARCHAR2(255),
    CONFIG_ALIAS    VARCHAR2(255),
    SMTP_HOST       VARCHAR2(255),
    SMTP_PORT       NUMBER(38, 0),
    NEED_AUTH       NUMBER(38, 0),
    EMAIL           VARCHAR2(255),
    DISPLAY_NAME    VARCHAR2(255),
    USER_NAME       VARCHAR2(255),
    PASSWORD        VARCHAR2(255),
    CONSTRAINT PK_BASE_MAIL_CONFIG PRIMARY KEY (CONFIG_ID)
)
;



-- 
-- TABLE: BASE_MAINTENANCE_LOG 
--

CREATE TABLE BASE_MAINTENANCE_LOG(
    MAINTENANCE_LOG_ID       VARCHAR2(255)    NOT NULL,
    OPERATOR_ID              VARCHAR2(50),
    OPERATOR_IP              VARCHAR2(50),
    OPERATION_DATE           TIMESTAMP(6),
    OPERATION_PATH           VARCHAR2(255),
    OPERATION_MODULE         VARCHAR2(255),
    OPERATION_TYPE           NUMBER(38, 0),
    BEFORE_OPERATION_DATA    CLOB,
    AFTER_OPERATION_DATA     CLOB,
    CONSTRAINT PK_BASE_MAINTENANCE_LOG PRIMARY KEY (MAINTENANCE_LOG_ID)
)
;



-- 
-- TABLE: BASE_NOTICE_TEMPLATE 
--

CREATE TABLE BASE_NOTICE_TEMPLATE(
    TEMPLATE_ID        VARCHAR2(255)     NOT NULL,
    TEMPLATE_TYPE      VARCHAR2(255),
    TEMPLATE_NAME      VARCHAR2(255),
    DESCRIPTION        VARCHAR2(1000),
    TEMPLATE_ALIAS     VARCHAR2(255),
    SUBJECT            VARCHAR2(255),
    CONTENT_CLOB_ID    NUMBER(38, 0),
    IS_ENABLED         NUMBER(38, 0),
    CONSTRAINT PK_BASE_NOTICE_TEMPLATE PRIMARY KEY (TEMPLATE_ID)
)
;



-- 
-- TABLE: BASE_OFFICE_HOUR 
--

CREATE TABLE BASE_OFFICE_HOUR(
    OFFICE_HOUR_ID       VARCHAR2(255)    NOT NULL,
    HOUR_OFFSET          VARCHAR2(255),
    WORK_START_HOUR      NUMBER(38, 0),
    WORK_START_MINUTE    NUMBER(38, 0),
    WORK_END_HOUR        NUMBER(38, 0),
    WORK_END_MINUTE      NUMBER(38, 0),
    SEAT_START_HOUR      NUMBER(38, 0),
    SEAT_START_MINUTE    NUMBER(38, 0),
    SEAT_END_HOUR        NUMBER(38, 0),
    SEAT_END_MINUTE      NUMBER(38, 0),
    WORK_TIME            NUMBER(30, 1),
    CONSTRAINT PK_BASE_OFFICE_HOUR PRIMARY KEY (OFFICE_HOUR_ID)
)
;



-- 
-- TABLE: BASE_RESOURCE 
--

CREATE TABLE BASE_RESOURCE(
    RESOURCE_ID            VARCHAR2(255)    NOT NULL,
    URL                    VARCHAR2(600),
    PARENT_ID              VARCHAR2(255),
    RESOURCE_NAME          VARCHAR2(600),
    DESCRIPTION            VARCHAR2(600),
    TREEPATH               VARCHAR2(600),
    ACTIVE_STATE           NUMBER(38, 0),
    ORDER_NUM              NUMBER(38, 0),
    IS_CONTAIN_LEAPNODE    NUMBER(38, 0),
    ICONCLS                VARCHAR2(255),
    IS_MENU                NUMBER(38, 0),
    BELONG_PLATFORM        VARCHAR2(255),
    RESOURCE_ALIAS         VARCHAR2(600),
    RESOURCE_CODE          VARCHAR2(255),
    CONSTRAINT PK_BASE_RESOURCE PRIMARY KEY (RESOURCE_ID)
)
;



-- 
-- TABLE: BASE_ROLE 
--

CREATE TABLE BASE_ROLE(
    ROLE_ID         VARCHAR2(255)    NOT NULL,
    ROLE_CODE       VARCHAR2(255),
    ROLE_NAME       VARCHAR2(255),
    DESCRIPTION     VARCHAR2(600),
    ROLE_TYPE       VARCHAR2(255),
    ACTIVE_STATE    NUMBER(38, 0)    DEFAULT 0 NOT NULL,
    ORDER_NUM       NUMBER(38, 0),
    CUSTOMFIELD1    VARCHAR2(255),
    CUSTOMFIELD2    VARCHAR2(255),
    CUSTOMFIELD3    NUMBER(38, 0),
    CUSTOMFIELD4    TIMESTAMP(6),
    CREATE_DATE     DATE,
    CONSTRAINT PK_BASE_ROLE PRIMARY KEY (ROLE_ID)
)
;



-- 
-- TABLE: BASE_ROLE_CONTROLSCOPE 
--

CREATE TABLE BASE_ROLE_CONTROLSCOPE(
    ID                 VARCHAR2(50)     NOT NULL,
    ROLE_ID            VARCHAR2(255),
    CONTROLSCOPE_ID    NUMBER(38, 0),
    ACTIVE_STATE       NUMBER(38, 0),
    SCOPE_ID           VARCHAR2(255)    NOT NULL,
    CONSTRAINT PK_UUM_ROLE_CONTROLSCOPE PRIMARY KEY (ID)
)
;



-- 
-- TABLE: BASE_ROLE_RESOURCE 
--

CREATE TABLE BASE_ROLE_RESOURCE(
    ROLE_RESOURCE_ID    VARCHAR2(255)    NOT NULL,
    RESOURCE_ID         VARCHAR2(255),
    ROLE_ID             VARCHAR2(255),
    ACTIVE_STATE        NUMBER(38, 0),
    CONSTRAINT PK_BASE_ROLE_RESOURCE PRIMARY KEY (ROLE_RESOURCE_ID)
)
;



-- 
-- TABLE: BASE_ROLE_USER 
--

CREATE TABLE BASE_ROLE_USER(
    ROLE_USER_ID    VARCHAR2(255)    NOT NULL,
    ROLE_ID         VARCHAR2(255)    NOT NULL,
    USERID          VARCHAR2(255)    NOT NULL,
    CONSTRAINT PK_BASE_ROLE_USER PRIMARY KEY (ROLE_USER_ID)
)
;



-- 
-- TABLE: BASE_SYSTEM_PARAMETER 
--

CREATE TABLE BASE_SYSTEM_PARAMETER(
    SYSTEM_PARAMETER_ID       VARCHAR2(255)     NOT NULL,
    SYSTEM_PARAMETER_NAME     VARCHAR2(100),
    SYSTEM_PARAMETER_VALUE    VARCHAR2(2000),
    CONSTRAINT PK_SYSTEM_PARAMETER_1 PRIMARY KEY (SYSTEM_PARAMETER_ID)
)
;



-- 
-- TABLE: BASE_USER 
--

CREATE TABLE BASE_USER(
    USERID                VARCHAR2(255)     NOT NULL,
    LOGIN_ID              VARCHAR2(255),
    PASSWORD              VARCHAR2(255),
    USER_NAME             VARCHAR2(255),
    SEX_CODE              VARCHAR2(255),
    BIRTHDAY              DATE,
    PHONE                 VARCHAR2(255),
    MOBILE_TELEPHONE      VARCHAR2(255),
    EMAIL                 VARCHAR2(255),
    FOLK_CODE             VARCHAR2(255),
    PAPER_TYPE_CODE       VARCHAR2(255),
    PAPER_NUM             VARCHAR2(255),
    POLITICES_CODE        VARCHAR2(255),
    DEGREE_CODE           VARCHAR2(255),
    DUTY_LEVEL_CODE       VARCHAR2(255),
    HIERARCHY             VARCHAR2(255),
    DUTY_REMARK           VARCHAR2(255),
    ZUGONG                NUMBER(38, 0),
    EXAM_STATE            NUMBER(38, 0),
    IS_BUREAU_RESERVE     NUMBER(38, 0),
    CURRENT_DUTY_TIME     VARCHAR2(50),
    SHIGUAN               NUMBER(38, 0),
    ACTIVE_STATE          NUMBER(38, 0),
    ORDER_NUM             NUMBER(38, 0),
    AVAILABILITY_STATE    NUMBER(38, 0),
    OFFICEHOLDING_DATE    DATE,
    DANGWU                NUMBER(38, 0),
    TONGZHAN              NUMBER(38, 0),
    VIEW_STATE            NUMBER(38, 0),
    REMARK                VARCHAR2(3000),
    GUID                  VARCHAR2(255),
    LEVEL_CODE            VARCHAR2(255),
    STUDY_STATE           NUMBER(38, 0),
    HEADER_PHOTO          VARCHAR2(255),
    OPERATE_TIME          TIMESTAMP(6),
    ENTRY_TIME            DATE,
    ADMINISTRATIVE_LEVEL  VARCHAR2(255),
    CONSTRAINT PK_BASE_USER PRIMARY KEY (USERID)
)
;

comment on column base_user.ADMINISTRATIVE_LEVEL is '所属地区行政级别编码';


-- 
-- TABLE: BASE_USER_MANAGE_SCOPE 
--

CREATE TABLE BASE_USER_MANAGE_SCOPE(
    SCOPE_ID        VARCHAR2(255)    NOT NULL,
    DEPID           VARCHAR2(255)    NOT NULL,
    USERID          VARCHAR2(255)    NOT NULL,
    ACTIVE_STATE    NUMBER(38, 0),
    CONSTRAINT PK_BASE_USER_MANAGE_SCOPE PRIMARY KEY (SCOPE_ID)
)
;



-- 
-- TABLE: BASE_WORK_DAY 
--

CREATE TABLE BASE_WORK_DAY(
    WORKDAY_ID           VARCHAR2(255)    NOT NULL,
    DATETIME             DATE,
    YEAR                 NUMBER(38, 0),
    MONTH                NUMBER(38, 0),
    DAY                  NUMBER(38, 0),
    WEEK                 NUMBER(38, 0),
    WEEK_OF_MONTH        NUMBER(38, 0),
    DAY_TYPE             NUMBER(38, 0),
    WEEK_OF_QUARTER      NUMBER(38, 0),
    QUARTER              NUMBER(38, 0),
    WORK_TIME            NUMBER(30, 1),
    WORK_START_HOUR      NUMBER(38, 0),
    WORK_START_MINUTE    NUMBER(38, 0),
    WORK_END_HOUR        NUMBER(38, 0),
    WORK_END_MINUTE      NUMBER(38, 0),
    SEAT_START_HOUR      NUMBER(38, 0),
    SEAT_START_MINUTE    NUMBER(38, 0),
    SEAT_END_HOUR        NUMBER(38, 0),
    SEAT_END_MINUTE      NUMBER(38, 0),
    REMARK               VARCHAR2(255),
    WORKYEAR_ID          VARCHAR2(255)    NOT NULL,
    CONSTRAINT PK_BASE_WORK_DAY PRIMARY KEY (WORKDAY_ID)
)
;



-- 
-- TABLE: BASE_WORK_YEAR 
--

CREATE TABLE BASE_WORK_YEAR(
    WORKYEAR_ID       VARCHAR2(255)    NOT NULL,
    WORKYEAR_NAME     VARCHAR2(255),
    WORKYEAR_CODE     VARCHAR2(255),
    YEAR              NUMBER(38, 0),
    REMARK            VARCHAR2(255),
    OFFICE_HOUR_ID    VARCHAR2(255)    NOT NULL,
    CONSTRAINT PK_BASE_WORK_YEAR PRIMARY KEY (WORKYEAR_ID)
)
;



-- 
-- TABLE: CLASS_AUDIT_INFO 
--

CREATE TABLE CLASS_AUDIT_INFO(
    AUDIT_ID         VARCHAR2(50)      NOT NULL,
    ADUIT_STATE      NUMBER(38, 0),
    AUDIT_REASON     VARCHAR2(2000),
    CREATE_DATE      DATE,
    ACTIVE_STATE     NUMBER(38, 0),
    CLASS_ID         VARCHAR2(50)      NOT NULL,
    CREATE_USERID    VARCHAR2(255)     NOT NULL,
    CONSTRAINT PK12 PRIMARY KEY (AUDIT_ID)
)
;



COMMENT ON COLUMN CLASS_AUDIT_INFO.CLASS_ID IS '班级ID'
;
COMMENT ON COLUMN CLASS_AUDIT_INFO.CREATE_USERID IS '创建人'
;
-- 
-- TABLE: DEPT_ASSIGNED_INFO 
--

CREATE TABLE DEPT_ASSIGNED_INFO(
    ID                     VARCHAR2(50)     NOT NULL,
    YEAR                   NUMBER(38, 0),
    RESTRICT_PERSON_NUM    NUMBER(38, 0),
    DEPID                  VARCHAR2(255),
    CLASS_TYPE             NUMBER(38, 0),
    CONSTRAINT PK_T_C_APPLYDEP PRIMARY KEY (ID)
)
;



COMMENT ON COLUMN DEPT_ASSIGNED_INFO.DEPID IS '部门Id'
;
COMMENT ON COLUMN DEPT_ASSIGNED_INFO.CLASS_TYPE IS '1:普通班    2:国防班'
;
-- 
-- TABLE: IMPORT_DATA_TRANSFER_LOG 
--

CREATE TABLE IMPORT_DATA_TRANSFER_LOG(
    LOGID            VARCHAR2(255)    NOT NULL,
    STORAGE_NAME     VARCHAR2(255),
    TRANSACT_DATE    DATE,
    RESULT           NUMBER(38, 0),
    CONSTRAINT PK_IMPORT_DATA_TRANSFER_LOG PRIMARY KEY (LOGID)
)
;



-- 
-- TABLE: IMPORT_ERROR_DATA 
--

CREATE TABLE IMPORT_ERROR_DATA(
    ERROR_DATA_ID    VARCHAR2(50)      NOT NULL,
    LOGID            VARCHAR2(255)     NOT NULL,
    CONTENT          VARCHAR2(2000),
    REASON           VARCHAR2(2000),
    CONSTRAINT PKIMPORT_ERROR_DATA PRIMARY KEY (ERROR_DATA_ID)
)
;



-- 
-- TABLE: INIT_USER 
--

CREATE TABLE INIT_USER(
    LOGINID    VARCHAR2(200),
    NAME       VARCHAR2(200)
)
;



-- 
-- TABLE: INTERIOR_USER 
--

CREATE TABLE INTERIOR_USER(
    USERID       VARCHAR2(255)    NOT NULL,
    USER_NAME    VARCHAR2(255),
    LOGIN_ID     VARCHAR2(255),
    CONSTRAINT PK33 PRIMARY KEY (USERID)
)
;



-- 
-- TABLE: LOCKS 
--

CREATE TABLE LOCKS(
    APPLICATION_ID     VARCHAR2(50)    NOT NULL,
    UNIQUE_ID          VARCHAR2(50),
    EXPIRATION_DATE    TIMESTAMP(6),
    CONSTRAINT LOCKS_PK PRIMARY KEY (APPLICATION_ID)
)
;



-- 
-- TABLE: PRE_AUTHORIZATION_REGISTRATION 
--

CREATE TABLE PRE_AUTHORIZATION_REGISTRATION(
    ID           VARCHAR2(255)    NOT NULL,
    USER_ID      VARCHAR2(255),
    LOGIN_ID     VARCHAR2(255),
    YEAR         NUMBER,
    USER_NAME    VARCHAR2(255),
    DEPID        VARCHAR2(255),
    CONSTRAINT PKK33 PRIMARY KEY (ID)
)
;



-- 
-- TABLE: PRE_DEPT_ASSIGNED_INFO 
--

CREATE TABLE PRE_DEPT_ASSIGNED_INFO(
    ID                     VARCHAR2(70)     NOT NULL,
    CLASS_TYPE             NUMBER(38, 0),
    YEAR                   NUMBER(38, 0),
    RESTRICT_PERSON_NUM    NUMBER(38, 0),
    DEPID                  VARCHAR2(255),
    CONSTRAINT PK_T_C_APPLYDEP_1 PRIMARY KEY (ID)
)
;



COMMENT ON COLUMN PRE_DEPT_ASSIGNED_INFO.CLASS_TYPE IS '1:普通班    2:国防班'
;
COMMENT ON COLUMN PRE_DEPT_ASSIGNED_INFO.DEPID IS '部门Id'
;
-- 
-- TABLE: QRTZ_BLOB_TRIGGERS 
--

CREATE TABLE QRTZ_BLOB_TRIGGERS(
    TRIGGER_NAME     VARCHAR2(80)    NOT NULL,
    TRIGGER_GROUP    VARCHAR2(80)    NOT NULL,
    BLOB_DATA        BLOB,
    CONSTRAINT SYS_C00450471 PRIMARY KEY (TRIGGER_NAME, TRIGGER_GROUP)
)
;



-- 
-- TABLE: QRTZ_CALENDARS 
--

CREATE TABLE QRTZ_CALENDARS(
    CALENDAR_NAME    VARCHAR2(80)    NOT NULL,
    CALENDAR         BLOB            NOT NULL,
    CONSTRAINT SYS_C00450474 PRIMARY KEY (CALENDAR_NAME)
)
;



-- 
-- TABLE: QRTZ_CRON_TRIGGERS 
--

CREATE TABLE QRTZ_CRON_TRIGGERS(
    TRIGGER_NAME       VARCHAR2(80)    NOT NULL,
    TRIGGER_GROUP      VARCHAR2(80)    NOT NULL,
    CRON_EXPRESSION    VARCHAR2(80)    NOT NULL,
    TIME_ZONE_ID       VARCHAR2(80),
    CONSTRAINT SYS_C00450478 PRIMARY KEY (TRIGGER_NAME, TRIGGER_GROUP)
)
;



-- 
-- TABLE: QRTZ_FIRED_TRIGGERS 
--

CREATE TABLE QRTZ_FIRED_TRIGGERS(
    ENTRY_ID             VARCHAR2(95)     NOT NULL,
    TRIGGER_NAME         VARCHAR2(80)     NOT NULL,
    TRIGGER_GROUP        VARCHAR2(80)     NOT NULL,
    IS_VOLATILE          VARCHAR2(1)      NOT NULL,
    INSTANCE_NAME        VARCHAR2(80)     NOT NULL,
    FIRED_TIME           NUMBER(13, 0)    NOT NULL,
    STATE                VARCHAR2(16)     NOT NULL,
    JOB_NAME             VARCHAR2(80),
    JOB_GROUP            VARCHAR2(80),
    IS_STATEFUL          VARCHAR2(1),
    REQUESTS_RECOVERY    VARCHAR2(1),
    CONSTRAINT SYS_C00450486 PRIMARY KEY (ENTRY_ID)
)
;



-- 
-- TABLE: QRTZ_JOB_DETAILS 
--

CREATE TABLE QRTZ_JOB_DETAILS(
    JOB_NAME             VARCHAR2(80)     NOT NULL,
    JOB_GROUP            VARCHAR2(80)     NOT NULL,
    DESCRIPTION          VARCHAR2(120),
    JOB_CLASS_NAME       VARCHAR2(128)    NOT NULL,
    IS_DURABLE           VARCHAR2(1)      NOT NULL,
    IS_VOLATILE          VARCHAR2(1)      NOT NULL,
    IS_STATEFUL          VARCHAR2(1)      NOT NULL,
    REQUESTS_RECOVERY    VARCHAR2(1)      NOT NULL,
    JOB_DATA             BLOB,
    CONSTRAINT SYS_C00450494 PRIMARY KEY (JOB_NAME, JOB_GROUP)
)
;



-- 
-- TABLE: QRTZ_JOB_LISTENERS 
--

CREATE TABLE QRTZ_JOB_LISTENERS(
    JOB_NAME        VARCHAR2(80)    NOT NULL,
    JOB_GROUP       VARCHAR2(80)    NOT NULL,
    JOB_LISTENER    VARCHAR2(80)    NOT NULL,
    CONSTRAINT SYS_C00450498 PRIMARY KEY (JOB_NAME, JOB_GROUP, JOB_LISTENER)
)
;



-- 
-- TABLE: QRTZ_LOCKS 
--

CREATE TABLE QRTZ_LOCKS(
    LOCK_NAME    VARCHAR2(40)    NOT NULL,
    CONSTRAINT SYS_C00450500 PRIMARY KEY (LOCK_NAME)
)
;



-- 
-- TABLE: QRTZ_PAUSED_TRIGGER_GRPS 
--

CREATE TABLE QRTZ_PAUSED_TRIGGER_GRPS(
    TRIGGER_GROUP    VARCHAR2(80)    NOT NULL,
    CONSTRAINT SYS_C00450502 PRIMARY KEY (TRIGGER_GROUP)
)
;



-- 
-- TABLE: QRTZ_SCHEDULER_STATE 
--

CREATE TABLE QRTZ_SCHEDULER_STATE(
    INSTANCE_NAME        VARCHAR2(80)     NOT NULL,
    LAST_CHECKIN_TIME    NUMBER(13, 0)    NOT NULL,
    CHECKIN_INTERVAL     NUMBER(13, 0)    NOT NULL,
    RECOVERER            VARCHAR2(80),
    CONSTRAINT SYS_C00450506 PRIMARY KEY (INSTANCE_NAME)
)
;



-- 
-- TABLE: QRTZ_SIMPLE_TRIGGERS 
--

CREATE TABLE QRTZ_SIMPLE_TRIGGERS(
    TRIGGER_NAME       VARCHAR2(80)     NOT NULL,
    TRIGGER_GROUP      VARCHAR2(80)     NOT NULL,
    REPEAT_COUNT       NUMBER(7, 0)     NOT NULL,
    REPEAT_INTERVAL    NUMBER(12, 0)    NOT NULL,
    TIMES_TRIGGERED    NUMBER(7, 0)     NOT NULL,
    CONSTRAINT SYS_C00450512 PRIMARY KEY (TRIGGER_NAME, TRIGGER_GROUP)
)
;



-- 
-- TABLE: QRTZ_TRIGGER_LISTENERS 
--

CREATE TABLE QRTZ_TRIGGER_LISTENERS(
    TRIGGER_NAME        VARCHAR2(80)    NOT NULL,
    TRIGGER_GROUP       VARCHAR2(80)    NOT NULL,
    TRIGGER_LISTENER    VARCHAR2(80)    NOT NULL,
    CONSTRAINT SYS_C00450525 PRIMARY KEY (TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_LISTENER)
)
;



-- 
-- TABLE: QRTZ_TRIGGERS 
--

CREATE TABLE QRTZ_TRIGGERS(
    TRIGGER_NAME      VARCHAR2(80)     NOT NULL,
    TRIGGER_GROUP     VARCHAR2(80)     NOT NULL,
    JOB_NAME          VARCHAR2(80)     NOT NULL,
    JOB_GROUP         VARCHAR2(80)     NOT NULL,
    IS_VOLATILE       VARCHAR2(1)      NOT NULL,
    DESCRIPTION       VARCHAR2(120),
    NEXT_FIRE_TIME    NUMBER(13, 0),
    PREV_FIRE_TIME    NUMBER(13, 0),
    TRIGGER_STATE     VARCHAR2(16)     NOT NULL,
    TRIGGER_TYPE      VARCHAR2(8)      NOT NULL,
    START_TIME        NUMBER(13, 0)    NOT NULL,
    END_TIME          NUMBER(13, 0),
    CALENDAR_NAME     VARCHAR2(80),
    MISFIRE_INSTR     NUMBER(2, 0),
    JOB_DATA          BLOB,
    CONSTRAINT SYS_C00450521 PRIMARY KEY (TRIGGER_NAME, TRIGGER_GROUP)
)
;



-- 
-- TABLE: SCHEDULE_LOG 
--

CREATE TABLE SCHEDULE_LOG(
    SCHEDULE_LOG_ID    VARCHAR2(255)     NOT NULL,
    JOB_NAME           VARCHAR2(255),
    EXECUTE_DATE       DATE,
    EXECUTE_RESULT     NUMBER(38, 0),
    EXCEPTION_STACK    VARCHAR2(4000),
    CONSTRAINT PK_SCHEDULE_LOG PRIMARY KEY (SCHEDULE_LOG_ID)
)
;



-- 
-- TABLE: SERVICETICKET 
--

CREATE TABLE SERVICETICKET(
    ID                         VARCHAR2(255)    NOT NULL,
    NUMBER_OF_TIMES_USED       NUMBER(10, 0),
    CREATION_TIME              NUMBER(19, 0),
    EXPIRATION_POLICY          BLOB             NOT NULL,
    LAST_TIME_USED             NUMBER(19, 0),
    PREVIOUS_LAST_TIME_USED    NUMBER(19, 0),
    FROM_NEW_LOGIN             NUMBER(1, 0)     NOT NULL,
    TICKET_ALREADY_GRANTED     NUMBER(1, 0)     NOT NULL,
    SERVICE                    BLOB             NOT NULL,
    TICKETGRANTINGTICKET_ID    VARCHAR2(255),
    CONSTRAINT SYS_C00450533 PRIMARY KEY (ID)
)
;



-- 
-- TABLE: SIGNUP_DEPT 
--

CREATE TABLE SIGNUP_DEPT(
    ID           VARCHAR2(70)     NOT NULL,
    DEPID        VARCHAR2(70),
    DEPNAME      VARCHAR2(500),
    PARENT_ID    VARCHAR2(70),
    TREEPATH     VARCHAR2(500),
    ORDER_NUM    NUMBER(38, 0),
    CONSTRAINT PK31 PRIMARY KEY (ID)
)
;



-- 
-- TABLE: TICKETGRANTINGTICKET 
--

CREATE TABLE TICKETGRANTINGTICKET(
    ID                            VARCHAR2(255)    NOT NULL,
    NUMBER_OF_TIMES_USED          NUMBER,
    CREATION_TIME                 NUMBER,
    EXPIRATION_POLICY             BLOB             NOT NULL,
    LAST_TIME_USED                NUMBER,
    PREVIOUS_LAST_TIME_USED       NUMBER,
    AUTHENTICATION                BLOB             NOT NULL,
    EXPIRED                       NUMBER           NOT NULL,
    SERVICES_GRANTED_ACCESS_TO    BLOB             NOT NULL,
    TICKETGRANTINGTICKET_ID       VARCHAR2(255),
    CONSTRAINT SYS_C00450541 PRIMARY KEY (ID)
)
;



-- 
-- TABLE: TRAINING_CLASS 
--

CREATE TABLE TRAINING_CLASS(
    CLASS_ID               VARCHAR2(100)     NOT NULL,
    CLASS_NAME             VARCHAR2(200),
    CLASSNAME_YEAR         VARCHAR2(200),
    CLASSNAME_PERIOD       VARCHAR2(50),
    CLASSNAME_UNIT         VARCHAR2(200),
    CLASS_MAIN_NAME        VARCHAR2(500),
    CLASS_NUM              VARCHAR2(60),
    CLASS_TYPE             VARCHAR2(50),
    CLASS_USER_NUM         NUMBER(38, 0),
    TRAINING_HOUR          NUMBER(18, 1),
    BEGIN_DATE             DATE,
    END_DATE               DATE,
    ENTER_BEGIN_TIME       DATE,
    ENTER_END_TIME         DATE,
    TRAINING_DEPT_ID       VARCHAR2(70),
    TRAINING_DEPT_NAME     VARCHAR2(70),
    APPLY_WAY              VARCHAR2(70),
    SPONSOR_ID             VARCHAR2(70),
    SPONSOR_NAME           VARCHAR2(200),
    TRAINING_ADDRESS       VARCHAR2(400),
    CLASS_DESCRIPTION      VARCHAR2(2000),
    PUBLISH_STATE          NUMBER(38, 0),
    OPEN_STATE             NUMBER(38, 0),
    END_STATE              NUMBER(38, 0),
    ACTIVE_STATE           NUMBER(38, 0),
    REPORT_STATE           NUMBER(38, 0),
    REPORT_DATE            DATE,
    AUDIT_STATE            NUMBER(38, 0),
    AUDIT_DATE             DATE,
    CREATE_DATE            DATE,
    LATEST_OPERATE_DATE    DATE,
    SIGNUP_STATE           NUMBER(38, 0),
    PERFECT_STATE          NUMBER(38, 0),
    ALLOTDEPT_STATE        NUMBER(38, 0),
    class_level			   VARCHAR2(50),
    MINISTRIE_ID           VARCHAR2(100),
    MINISTRIE_NAME		   VARCHAR2(255),
    FAX                    VARCHAR2(100),
    CONTACT_TEL            VARCHAR2(100),
    CONTACT_PEOPLE         VARCHAR2(100),
    JOINT_MINISTRIE_NAME   VARCHAR2(255),
    JOINT_MINISTRIE_ID     VARCHAR2(100),
    SELECTED_LETTERS       VARCHAR2(255),
    EXPECT_TIME            VARCHAR2(255),
    ASSESS_START_TIME	   DATE,--评估开始时间
    ASSESS_END_TIME		   DATE,--评估结束时间
    QUESTIONNAIRE_URL      VARCHAR2(1000),
    PHONE		   		   VARCHAR2(255),
    SIGNUPOR			   NUMBER(38, 0),	
    QUESTIONNAIRE_METHOD NUMBER(2, 0),	
    CONSTRAINT PK_VNA_THEMATIC_CLASS PRIMARY KEY (CLASS_ID)
)
;

-- Add/modify columns 
alter table TRAINING_CLASS add lock_state NUMBER(2);
-- Add comments to the columns 
comment on column TRAINING_CLASS.lock_state
  is '考核管理锁定状态';

  
-- Add comments to the columns 
comment on column TRAINING_CLASS.selected_letters
  is '选调函';

-- Add comments to the columns 
comment on column TRAINING_CLASS.expect_time
  is '计划时间';


-- 
-- TABLE: TRAINING_CLASS_COURSE 
--

CREATE TABLE TRAINING_CLASS_COURSE(
    CLASS_COURSE_ID    VARCHAR2(50)      NOT NULL,
    COURSE_NAME        VARCHAR2(255),
    COURSE_NUM         VARCHAR2(255),
    STUDY_SCORE        NUMBER(18, 1),
    SPEAKER            VARCHAR2(255),
    DUTY_REMARK        VARCHAR2(255),
    SPEAKDEPT          VARCHAR2(500),
    SPEAKWAY           VARCHAR2(500),
    CONTENT            VARCHAR2(2000),
    CLASS_ID           VARCHAR2(50)      NOT NULL,
    CREATE_DATE        DATE,
    SPEAK_DATE		   DATE,
    SPEAK_TIME		   VARCHAR2(500),
    SPEAK_DUTY		   VARCHAR2(500),
    CONSTRAINT PK28 PRIMARY KEY (CLASS_COURSE_ID)
)
;
-- Add/modify columns 
alter table TRAINING_CLASS_COURSE add course_assess_start_time date;
alter table TRAINING_CLASS_COURSE add course_assess_end_time date;


COMMENT ON COLUMN TRAINING_CLASS_COURSE.SPEAKER IS '多个讲师用逗号分隔'
;
COMMENT ON COLUMN TRAINING_CLASS_COURSE.CLASS_ID IS '培训班ID'
;
-- 
-- TABLE: TRAINING_CLASS_DEPT 
--

CREATE TABLE TRAINING_CLASS_DEPT(
    CLASS_DEPT_ID    VARCHAR2(70)     NOT NULL,
    CLASS_ID         VARCHAR2(70)     NOT NULL,
    DEPID            VARCHAR2(70),
    LIMIT_NUM        NUMBER(10, 0),
    CONSTRAINT PK32 PRIMARY KEY (CLASS_DEPT_ID)
)
;



-- 
-- TABLE: TRAINING_CLASS_USER 
--

CREATE TABLE TRAINING_CLASS_USER(
    CLASS_USER_ID         VARCHAR2(60)     NOT NULL,
    CLASS_ID              VARCHAR2(50),
    ACTIVE_STATE          NUMBER(38, 0),
    CREATE_DATE           DATE,
    auditing_Date		DATE,
    USER_ID               VARCHAR2(255),
    AUDITING_STATE        NUMBER(38, 0),
    AUDITING_USER_DATE    DATE,
    AUDITING_USER_IP	VARCHAR2(50),
    AUDITING_USER_ID	VARCHAR2(50),
    SIGNUP_YEAR           VARCHAR2(100),
    ATTENDANCE_STATE      NUMBER(38, 0),
    SIGN_UP_TYPE          NUMBER(38, 0),
    TRAINING_HOURS	NUMBER(10,2),
    EXAMINATION_REMARK VARCHAR2(3000),
    CONSTRAINT PK_T_C_USER PRIMARY KEY (CLASS_USER_ID)
)
;



COMMENT ON COLUMN TRAINING_CLASS_USER.CLASS_ID IS '培训班用户关系ID'
;
COMMENT ON COLUMN TRAINING_CLASS_USER.ACTIVE_STATE IS '活动状态'
;
COMMENT ON COLUMN TRAINING_CLASS_USER.USER_ID IS '用户Id'
;
COMMENT ON COLUMN TRAINING_CLASS_USER.ATTENDANCE_STATE IS '考勤状态'
;
-- 
-- TABLE: TRAINING_NOTICE 
--

CREATE TABLE TRAINING_NOTICE(
    NOTICE_ID        VARCHAR2(70)    NOT NULL,
    ATTACHMENT_ID    VARCHAR2(70),
    CLASS_ID         VARCHAR2(70)    NOT NULL,
    CONSTRAINT PK29 PRIMARY KEY (NOTICE_ID)
)
;



COMMENT ON COLUMN TRAINING_NOTICE.CLASS_ID IS '培训班ID'
;
-- 
-- TABLE: WF_PROCESS_DEFINITION 
--

CREATE TABLE WF_PROCESS_DEFINITION(
    PROCESS_ID      VARCHAR2(255)    NOT NULL,
    PROCESS_CODE    VARCHAR2(255),
    PROCESS_NAME    VARCHAR2(255),
    FORM_PATH       VARCHAR2(255),
    OPEN_TYPE       NUMBER(38, 0),
    CONSTRAINT PK_WF_PROCESS_DEFINITION PRIMARY KEY (PROCESS_ID)
)
;



-- 
-- TABLE: WF_TASK_DEFINITON 
--

CREATE TABLE WF_TASK_DEFINITON(
    TASK_ID       VARCHAR2(255)    NOT NULL,
    TASK_CODE     VARCHAR2(255),
    TASK_NAME     VARCHAR2(255),
    PROCESS_ID    VARCHAR2(255)    NOT NULL,
    CONSTRAINT PK_WF_TASK_DEFINITON PRIMARY KEY (TASK_ID)
)
;



-- 
-- TABLE: WF_TASK_INSTANCE 
--

CREATE TABLE WF_TASK_INSTANCE(
    TASK_INSTANCE_ID       VARCHAR2(255)    NOT NULL,
    INSTANCE_ID            VARCHAR2(255),
    TASK_CODE              VARCHAR2(255),
    TASK_NAME              VARCHAR2(255),
    PROCESS_NAME           VARCHAR2(255),
    PROCESS_CODE           VARCHAR2(255),
    PROCESS_INSTANCE_ID    VARCHAR2(50),
    FORM_NUMBER            VARCHAR2(255),
    ASSIGNED_DATE          DATE,
    PLAN_FINISH_DATE       DATE,
    HANDLE_DESCRIPTION     VARCHAR2(255),
    TASK_REMARK            VARCHAR2(255),
    INITIATOR_ID           VARCHAR2(255),
    INITIATOR_NAME         VARCHAR2(255),
    INITIATOR_DATE         DATE,
    OPERATOR_ID            VARCHAR2(255),
    OPERATOR_NAME          VARCHAR2(255),
    OPERATION_TYPE         VARCHAR2(255),
    FINISH_DATE            DATE,
    ENTRUSTED_ID           VARCHAR2(255),
    ENTRUSTED_NAME         VARCHAR2(255),
    ENTRUSTED_DATE         DATE,
    EXECUTOR_ID            VARCHAR2(255),
    EXECUTOR_NAME          VARCHAR2(255),
    EXECUTOR_DATE          DATE,
    STATE                  NUMBER(38, 0),
    CONSTRAINT PK_WF_TASK_INSTANCE PRIMARY KEY (TASK_INSTANCE_ID)
)
;

CREATE TABLE TRAINING_CLASS_PLAN_APPLY(
    PLAN_APPLY_ID    VARCHAR2(50)     NOT NULL,
    DEPT_ID          VARCHAR2(50),
    DEPT_CODE        VARCHAR2(300),
    DEPT_NAME        VARCHAR2(300),
    PLAN_COUNT       NUMBER(38, 0),
    ASIGN_COUNT      NUMBER(38, 0),
    CLASS_ID         VARCHAR2(100)    NOT NULL,
    CONSTRAINT PK_TRAINING_CLASS_PLAN_APPLY PRIMARY KEY (PLAN_APPLY_ID)
)
;


--ALTER TABLE TRAINING_CLASS_PLAN_APPLY ADD CONSTRAINT RefTRAINING_CLASS1 
--    FOREIGN KEY (CLASS_ID)
--    REFERENCES TRAINING_CLASS(CLASS_ID) ON DELETE CASCADE
--;

--培训班用户关系增加临时用户名
alter table TRAINING_CLASS_USER add USER_NAME VARCHAR2(255);
alter table TRAINING_CLASS_USER add SEX_CODE VARCHAR2(255);
alter table TRAINING_CLASS_USER add duty_Level_Code VARCHAR2(255);
alter table TRAINING_CLASS_USER add BIRTHDAY DATE;
alter table TRAINING_CLASS_USER add MOBILE_TELEPHONE VARCHAR2(255);
alter table TRAINING_CLASS_USER add FOLK_CODE VARCHAR2(255);
alter table TRAINING_CLASS_USER add DUTY_REMARK VARCHAR2(255);
alter table TRAINING_CLASS_USER add REMARK VARCHAR2(3000);
alter table TRAINING_CLASS_USER add REJECT_REASON VARCHAR2(3000);
	
-- Add/modify columns 
alter table TRAINING_CLASS_PLAN_APPLY add city_plan_count NUMBER(38);
alter table TRAINING_CLASS_PLAN_APPLY add county_plan_count NUMBER(38);

alter table TRAINING_CLASS_PLAN_APPLY add ASIGN_CITY_COUNT NUMBER(38);
alter table TRAINING_CLASS_PLAN_APPLY add ASIGN_COUNTY_COUNT NUMBER(38);



-- 
-- TABLE: QUESTION_BANK 
--

CREATE TABLE QUESTION_BANK(
    QUESTION_ID             VARCHAR2(50)     NOT NULL,
    QUESTION_TYPE           VARCHAR2(50),
    QUESTION_NAME           VARCHAR2(500),
    USERID                  VARCHAR2(255)    NOT NULL,
    CREATE_DATE             TIMESTAMP(6),
    ACTIVE_STATE            NUMBER(38, 0),
    WORD_NUM                NUMBER(38, 0),
    IS_SCORE_TYPE           NUMBER(38, 0),
    QUESTIONS_PROBLEM_SETS  VARCHAR2(500),
    CONSTRAINT PK54 PRIMARY KEY (QUESTION_ID)
)
;



-- 
-- TABLE: QUESTION_BANK_OPTION 
--

CREATE TABLE QUESTION_BANK_OPTION(
    OPTION_ID       VARCHAR2(50)     NOT NULL,
    QUESTION_ID     VARCHAR2(50)     NOT NULL,
    OPTION_NAME     VARCHAR2(500),
    OPTION_NUM      VARCHAR2(10),
    CREATE_DATE     TIMESTAMP(6),
    ACTIVE_STATE    NUMBER(38, 0),
    OBJECTIVE_TOPIC			NUMBER(38, 0),
    CONSTRAINT PK55 PRIMARY KEY (OPTION_ID)
)
;



-- 
-- TABLE: questionnaire 
--

CREATE TABLE questionnaire(
    QUESTIONNAIRE_ID         VARCHAR2(50)      NOT NULL,
    QUESTIONNAIRE_NAME       VARCHAR2(50),
    QUESTIONNAIRE_TYPE       VARCHAR2(255),
    SURVEY_OBJECT_TYPE       NUMBER(38, 0),
    BEGIN_DATE               TIMESTAMP(6),
    END_DATE                 TIMESTAMP(6),
    USERID                   VARCHAR2(255)     NOT NULL,
    DEPID                    VARCHAR2(255)     NOT NULL,
    SURVEY_TYPE              NUMBER(38, 0),
    CREATE_DATE              TIMESTAMP(6),
    QUESTIONNAIRE_EXPLAIN    VARCHAR2(2000),
    PUBLISH_STATE            NUMBER(38, 0),
    ACTIVE_STATE             NUMBER(38, 0),
    CONSTRAINT PK56 PRIMARY KEY (QUESTIONNAIRE_ID)
)
;



-- 
-- TABLE: questionnaire_question 
--

CREATE TABLE questionnaire_question(
    QUESTIONNAIRE_QUESTION_ID    VARCHAR2(50)     NOT NULL,
    QUESTIONNAIRE_ID             VARCHAR2(50)     NOT NULL,
    QUESTION_ID                  VARCHAR2(50)     NOT NULL,
    IS_MUST                      NUMBER(38, 0),
    ORDER_NUM                    NUMBER(38, 0),
    CREATE_DATE                  TIMESTAMP(6),
    ACTIVE_STATE                 NUMBER(38, 0),
    CONSTRAINT PK57 PRIMARY KEY (QUESTIONNAIRE_QUESTION_ID)
)
;



-- 
-- TABLE: questionnaire_question_type 
--

CREATE TABLE questionnaire_question_type(
    QUESTION_TYPE_ID    VARCHAR2(50)     NOT NULL,
    QUESTIONNAIRE_ID    VARCHAR2(50)     NOT NULL,
    QUESTION_TYPE       VARCHAR2(50),
    CREATE_DATE         TIMESTAMP(6),
    ACTIVE_STATE        NUMBER(38, 0),
    CONSTRAINT PK58 PRIMARY KEY (QUESTION_TYPE_ID)
)
;



-- 
-- TABLE: questionnaire_survey_object 
--

CREATE TABLE questionnaire_survey_object(
    QUESTIONNAIRE_SURVEY_OBJECT_ID    VARCHAR2(50)     NOT NULL,
    QUESTIONNAIRE_ID                  VARCHAR2(50)     NOT NULL,
    SURVEY_OBJECT_ID                  VARCHAR2(255),
    SURVEY_OBJECT_TYPE                NUMBER(38, 0),
    DUTY_CODES                        VARCHAR2(500),
    WRITE_TYPE                        NUMBER(38, 0),
    CREATE_DATE                       TIMESTAMP(6),
    ACTIVE_STATE                      NUMBER(38, 0),
    CONSTRAINT PK59 PRIMARY KEY (QUESTIONNAIRE_SURVEY_OBJECT_ID)
)
;



-- 
-- TABLE: questionnaire_user_answer 
--

CREATE TABLE questionnaire_user_answer(
    USER_ANSWER_ID      VARCHAR2(50)      NOT NULL,
    USERID              VARCHAR2(255)     NOT NULL,
    QUESTIONNAIRE_ID    VARCHAR2(50)      NOT NULL,
    QUESTION_ID         VARCHAR2(50)      NOT NULL,
    QUESTION_ANSWER     VARCHAR2(2000),
    QUESTION_OPTION     VARCHAR2(50),
    CREATE_DATE         TIMESTAMP(6),
    ACTIVE_STATE        NUMBER(38, 0),
    CLASS_ID            VARCHAR2(100),
    CONSTRAINT PK60 PRIMARY KEY (USER_ANSWER_ID)
)
;



-- 
-- TABLE: questionnaire_user_answer_temp 
--

CREATE TABLE questionnaire_user_answer_temp(
    USER_ANSWER_ID      VARCHAR2(50)      NOT NULL,
    USERID              VARCHAR2(255)     NOT NULL,
    QUESTIONNAIRE_ID    VARCHAR2(50)      NOT NULL,
    QUESTION_ID         VARCHAR2(50)      NOT NULL,
    QUESTION_ANSWER     VARCHAR2(2000),
    QUESTION_OPTION     VARCHAR2(50),
    CREATE_DATE         TIMESTAMP(6),
    ACTIVE_STATE        NUMBER(38, 0),
    CONSTRAINT PK61 PRIMARY KEY (USER_ANSWER_ID)
)
;

--
-- ER/Studio 8.0 SQL Code Generation
-- Company :      Microsoft
-- Project :      部委选学报名系统设计.DM1
-- Author :       AutoBVT
--
-- Date Created : Friday, May 06, 2016 14:40:39
-- Target DBMS : Oracle 11g
--

-- 
-- TABLE: NEWS 
--

CREATE TABLE NEWS(
    NEWS_ID            VARCHAR2(50)     NOT NULL,
    SEND_LOOK_STATE    NUMBER(2, 0),
    NEWS_VALUE         VARCHAR2(2000),
    ACTIVE_STATE       NUMBER(2, 0),
    CREATE_DATE        TIMESTAMP(6),
    SEND_USER_ID        VARCHAR2(50),
    CLASS_NAME        VARCHAR2(255),
    ATTACHMENT_ID       VARCHAR2(50),
    NEWS_TITLE	VARCHAR2(255),
    NEWS_CODE           VARCHAR2(50),
    FILE_SIZE			VARCHAR2(50),
    CONSTRAINT PK68 PRIMARY KEY (NEWS_ID)
)
;

-- 
-- TABLE: NEWS_USER_LINK 
--

CREATE TABLE NEWS_USER_LINK(
    NEWS_USER_ID          VARCHAR2(50)    NOT NULL,
    READ_STATE            NUMBER(2, 0),
    REPLY_STATE           NUMBER(2, 0),
    RECEIVE_LOOK_STATE    NUMBER(2, 0),
    CREATE_DATE           TIMESTAMP(6),
    NEWS_ID               VARCHAR2(50),
    ACTIVE_STATE       NUMBER(2, 0),
    REPLY_USER_ID        VARCHAR2(50),
    USER_ID              VARCHAR2(50),
    RECEIVE_TIME           TIMESTAMP(6),
    CONSTRAINT PK69 PRIMARY KEY (NEWS_USER_ID)
)
;

-- 
-- TABLE: REMIND 
--

CREATE TABLE REMIND(
    REMIND_ID       VARCHAR2(50)     NOT NULL,
    REMIND_STATE    NUMBER(2, 0),
    REMIND_VALUE    VARCHAR2(500),
    ACTIVE_STATE    NUMBER(2, 0),
    CREATE_DATE     TIMESTAMP(6),
    REMIND_CODE     VARCHAR2(50),
    CATEGORY_CODE   VARCHAR2(50),
    CONSTRAINT PK63 PRIMARY KEY (REMIND_ID)
)
;

-- 
-- TABLE: REMIND_USER_LINK 
--

CREATE TABLE REMIND_USER_LINK(
    REMIND_USER_ID    VARCHAR2(50)     NOT NULL,
    LOOK_STATE        NUMBER(2, 0),
    ACTIVE_STATE      NUMBER(2, 0),
    CREATE_DATE       TIMESTAMP(6),
    REMIND_VALUE      VARCHAR2(500),
    REMIND_ID         VARCHAR2(50),
    REPLY_USER_ID        VARCHAR2(50),
    send_permit NUMBER(2, 0),
    remind_classIdcode VARCHAR2(100),
    CONSTRAINT PK64 PRIMARY KEY (REMIND_USER_ID)
);

-- 
-- TABLE: REPLY 
--

CREATE TABLE REPLY(
    REPLY_ID        VARCHAR2(50)     NOT NULL,
    REPLY_VALUE     VARCHAR2(255),
    CREATE_DATE     TIMESTAMP(6),
    NEWS_USER_ID    VARCHAR2(50),
    USER_ID         VARCHAR2(50),
    ACTIVE_STATE       NUMBER(2, 0),
    READ_STATE            NUMBER(2, 0),
    CONSTRAINT PK71 PRIMARY KEY (REPLY_ID)
)
;

-- 
-- TABLE: NEWS_USER_LINK 
--

ALTER TABLE NEWS_USER_LINK ADD CONSTRAINT RefNEWS13 
    FOREIGN KEY (NEWS_ID)
    REFERENCES NEWS(NEWS_ID)
;

-- 
-- TABLE: REMIND_USER_LINK 
--

ALTER TABLE REMIND_USER_LINK ADD CONSTRAINT RefREMIND16 
    FOREIGN KEY (REMIND_ID)
    REFERENCES REMIND(REMIND_ID)
;

-- 
-- TABLE: REPLY 
--

ALTER TABLE REPLY ADD CONSTRAINT RefNEWS_USER_LINK23 
    FOREIGN KEY (NEWS_USER_ID)
    REFERENCES NEWS_USER_LINK(NEWS_USER_ID)
;

--填写计划意见表
CREATE TABLE plan_Opinion(
    OPINION_ID       VARCHAR2(50)     NOT NULL,
    Opinion_title    VARCHAR2(100),
    Opinion_value    VARCHAR2(255),
    CREATE_DATE      TIMESTAMP(6),
    active_state 	NUMBER(2),
    OPINION_STATE NUMBER(2),
    Issued_ID	VARCHAR2(50) NOT NULL,
    CONSTRAINT PK71_1 PRIMARY KEY (OPINION_ID)
)
;

--计划下发表
CREATE TABLE plan_Issued(
    Issued_ID       VARCHAR2(50)     NOT NULL,
    CREATE_DATE      TIMESTAMP(6),
    active_state 	NUMBER(2),
    plan_id    VARCHAR2(50)     NOT NULL,
    dept_id	VARCHAR2(50) NOT NULL,
    feedback_State NUMBER(2),
    opinion VARCHAR2(500),
    CONSTRAINT PK71_10 PRIMARY KEY (Issued_ID)
)
;

alter table PLAN_OPINION
  add constraint FK70_1 foreign key (ISSUED_ID)
  references PLAN_ISSUED (ISSUED_ID);
-- 
-- TABLE: training_Plan 
--
--培训计划
CREATE TABLE training_Plan(
    Plan_ID             VARCHAR2(50)     NOT NULL,
    apply_num           VARCHAR2(50),
    class_NAME          VARCHAR2(100),
    CLASS_objective     VARCHAR2(1000),
    CLASS_VALUES        VARCHAR2(1000),
    plan_num            NUMBER(5, 0),
    plan_day            NUMBER(6, 1),
    fax							VARCHAR2(50),
--    BEGIN_DATE          DATE,
--    END_DATE           DATE,
--    ENTER_BEGIN_TIME    DATE,
    Joint_name          VARCHAR2(50),
    undertake_Name      VARCHAR2(50),
    Joint_id          VARCHAR2(50),
    undertake_id      VARCHAR2(50),
    --plan_date           VARCHAR2(50),
    start_Plan_Date	TIMESTAMP(6),
    end_Plan_Date	TIMESTAMP(6),
    Training_object     VARCHAR2(1000),
    Reject_Reason       VARCHAR2(1000),
    TRAINING_ADDRESS    VARCHAR2(300),
    Issued_date        TIMESTAMP(6),
    Issued_state        NUMBER(2, 0),
    PUBLISH_STATE       NUMBER(2, 0),
    publish_date        TIMESTAMP(6),
    AUDIT_STATE         NUMBER(2, 0),
    AUDIT_DATE          TIMESTAMP(6),
    ACTIVE_STATE        NUMBER(2, 0),
    CREATE_DATE         TIMESTAMP(6),
    create_user			varchar2(30),
    Reserve_field1		varchar2(200),
    Reserve_field2		DATE,
    version		number(3,1),
    link_mode varchar2(100),
    link_man varchar2(50),
    operation_Date	TIMESTAMP(6),
    CONSTRAINT PK_VNA_THEMATIC_CLASS_111 PRIMARY KEY (Plan_ID)
);

-- 
-- TABLE: training_Plan 
--
--培训计划部委归档
CREATE TABLE training_Plan_File(
    Plan_ID             VARCHAR2(50)     NOT NULL,
    apply_num           VARCHAR2(50),
    class_NAME          VARCHAR2(100),
    CLASS_objective     VARCHAR2(1000),
    CLASS_VALUES        VARCHAR2(1000),
    plan_num            NUMBER(5, 0),
    plan_day            NUMBER(6, 1),
    fax							VARCHAR2(50),
--    BEGIN_DATE          DATE,
--    END_DATE           DATE,
--    ENTER_BEGIN_TIME    DATE,
    Joint_name          VARCHAR2(50),
    undertake_Name      VARCHAR2(50),
    Joint_id          VARCHAR2(50),
    undertake_id      VARCHAR2(50),
    --plan_date           VARCHAR2(50),
    start_Plan_Date	TIMESTAMP(6),
    end_Plan_Date	TIMESTAMP(6),
    Training_object     VARCHAR2(1000),
    Reject_Reason       VARCHAR2(1000),
    TRAINING_ADDRESS    VARCHAR2(300),
    Issued_date        TIMESTAMP(6),
    Issued_state        NUMBER(2, 0),
    PUBLISH_STATE       NUMBER(2, 0),
    publish_date        TIMESTAMP(6),
    AUDIT_STATE         NUMBER(2, 0),
    AUDIT_DATE          TIMESTAMP(6),
    ACTIVE_STATE        NUMBER(2, 0),
    CREATE_DATE         TIMESTAMP(6),
    create_user			varchar2(30),
    Reserve_field1		varchar2(200),
    Reserve_field2		DATE,
    version		number(3,1),
    plan_apply blob,
    link_mode varchar2(100),
    link_man varchar2(50),
    operation_Date	TIMESTAMP(6),
    CONSTRAINT PK_VNA_THEMATIC_CLASS_2 PRIMARY KEY (Plan_ID)
);
-- 
-- TABLE: training_Plan 
--
--培训计划中组部归档
CREATE TABLE training_Plan_File_ZZB(
    Plan_ID             VARCHAR2(50)     NOT NULL,
    apply_num           VARCHAR2(50),
    class_NAME          VARCHAR2(100),
    CLASS_objective     VARCHAR2(1000),
    CLASS_VALUES        VARCHAR2(1000),
    plan_num            NUMBER(5, 0),
    plan_day            NUMBER(6, 1),
    fax							VARCHAR2(50),
--    BEGIN_DATE          DATE,
--    END_DATE           DATE,
--    ENTER_BEGIN_TIME    DATE,
    Joint_name          VARCHAR2(50),
    undertake_Name      VARCHAR2(50),
    Joint_id          VARCHAR2(50),
    undertake_id      VARCHAR2(50),
    --plan_date           VARCHAR2(50),
    start_Plan_Date	TIMESTAMP(6),
    end_Plan_Date	TIMESTAMP(6),
    Training_object     VARCHAR2(1000),
    Reject_Reason       VARCHAR2(1000),
    TRAINING_ADDRESS    VARCHAR2(300),
    Issued_date        TIMESTAMP(6),
    Issued_state        NUMBER(2, 0),
    PUBLISH_STATE       NUMBER(2, 0),
    publish_date        TIMESTAMP(6),
    AUDIT_STATE         NUMBER(2, 0),
    AUDIT_DATE          TIMESTAMP(6),
    ACTIVE_STATE        NUMBER(2, 0),
    CREATE_DATE         TIMESTAMP(6),
    create_user			varchar2(30),
    Reserve_field1		varchar2(200),
    Reserve_field2		DATE,
    version		number(3,1),
    plan_apply blob,
    link_mode varchar2(100),
    link_man varchar2(50),
    operation_Date	TIMESTAMP(6),
    CONSTRAINT PK_VNA_THEMATIC_CLASS_3 PRIMARY KEY (Plan_ID)
);

--考勤关联表
create table TRAINING_CLASS_ATTENDANCE
(
  att_id      VARCHAR2(50) not null,
  class_id    VARCHAR2(50),
  user_id     VARCHAR2(50),
  create_date DATE,
  user_state  NUMBER(2),
  CONSTRAINT ATT_PRIMARY_ID PRIMARY KEY (att_id)
);
comment on column TRAINING_CLASS_ATTENDANCE.att_id
  is '考勤表主键';
comment on column TRAINING_CLASS_ATTENDANCE.class_id
  is '班级id';
comment on column TRAINING_CLASS_ATTENDANCE.user_id
  is '人员id';
comment on column TRAINING_CLASS_ATTENDANCE.create_date
  is '创建时间';
comment on column TRAINING_CLASS_ATTENDANCE.user_state
  is '人员状态';
  
  -- 承办部委关联表
  create table CLASS_SIGN_UP_DEPARTMENT
(
  SIGN_UP_dept_id VARCHAR2(50) not null,
  class_id      VARCHAR2(50),
  dept_id       VARCHAR2(50),
  active_State  number(2),
  create_Date TIMESTAMP(6)
);
alter table CLASS_SIGN_UP_DEPARTMENT
  add constraint SCOPE_PK_ID2 primary key (SIGN_UP_dept_id);
  alter table CLASS_SIGN_UP_DEPARTMENT
  add constraint CLASS_FK_ID2 foreign key (CLASS_ID)
  references TRAINING_CLASS (CLASS_ID);
alter table CLASS_SIGN_UP_DEPARTMENT
  add constraint DEPT_FK_ID2 foreign key (DEPT_ID)
  references BASE_DEPARTMENT (DEPID);
  
  -- 报名机构范围关联表
  create table class_scope_DEPARTMENT
(
  scope_dept_id VARCHAR2(50) not null,
  class_id      VARCHAR2(50),
  dept_id       VARCHAR2(50),
  active_State  number(2),
  create_Date TIMESTAMP(6)
);
alter table class_scope_DEPARTMENT
  add constraint SCOPE_PK_ID primary key (scope_dept_id);
  alter table class_scope_DEPARTMENT
  add constraint CLASS_FK_ID foreign key (CLASS_ID)
  references TRAINING_CLASS (CLASS_ID);
alter table class_scope_DEPARTMENT
  add constraint DEPT_FK_ID foreign key (DEPT_ID)
  references BASE_DEPARTMENT (DEPID);
  
alter table TRAINING_CLASS_USER add examination_result VARCHAR2(100);
comment on column TRAINING_CLASS_USER.examination_result is '考勤结果';
  
alter table TRAINING_CLASS add category_id VARCHAR2(40);
alter table TRAINING_CLASS add beoff_station VARCHAR2(40);
comment on column TRAINING_CLASS.category_id is '培训类别id';
comment on column TRAINING_CLASS.beoff_station is '离岗状态';

-- Create table
create table Departmental_level
(
  depid       varchar2(255)    NOT NULL,
  depid_one   varchar2(255),
  depname_one varchar2(255),
  depid_two   varchar2(255),
  depname_two varchar2(255),
CONSTRAINT PK_Departmental_level PRIMARY KEY (depid)
)
;

CREATE OR REPLACE PROCEDURE update_Departmental_level IS
BEGIN
  --查询所有部门
  FOR dep IN (select depid from base_department) LOOP
    INSERT INTO Departmental_level
    --本部门ID，一级部门ID，一级部门名称，二级部门ID，二级部门名称
      (DEPID, DEPID_ONE, DEPNAME_ONE, DEPID_TWO, DEPNAME_TWO)
    VALUES
      (dep.depid,
       (select *
          from ((select t.depid
                   from (select bd.depid,
                                bd.depname,
                                bd.treepath,
                                bd.admin_level_code
                           from base_department bd
                          start with bd.depid = dep.depid
                         connect by prior bd.parent_id = bd.depid) t
                  where t.admin_level_code = 1
                    and rownum = 1
                  order by t.treepath))),
       (select *
          from ((select t.depname
                   from (select bd.depid,
                                bd.depname,
                                bd.treepath,
                                bd.admin_level_code
                           from base_department bd
                          start with bd.depid = dep.depid
                         connect by prior bd.parent_id = bd.depid) t
                  where t.admin_level_code = 1
                    and rownum = 1
                  order by t.treepath))),
       (select *
          from ((select t.depid
                   from (select bd.depid,
                                bd.depname,
                                bd.treepath,
                                bd.admin_level_code
                           from base_department bd
                          start with bd.depid = dep.depid
                         connect by prior bd.parent_id = bd.depid) t
                  where t.admin_level_code = 2
                    and rownum = 1
                  order by t.treepath))),
       (select *
          from ((select t.depname
                   from (select bd.depid,
                                bd.depname,
                                bd.treepath,
                                bd.admin_level_code
                           from base_department bd
                          start with bd.depid = dep.depid
                         connect by prior bd.parent_id = bd.depid) t
                  where t.admin_level_code = 2
                    and rownum = 1
                  order by t.treepath))));
    commit;
  end loop;
  DBMS_OUTPUT.PUT_LINE('更新部门级别成功');
end update_Departmental_level;